
from mysql_client import MySQLSSHClient
import html
class CpaData:
    def __init__(self):
        self.client = MySQLSSHClient()


    def get_active_new_data(self):
        # 方法1: 常规使用
        if self.client.connect():
            try:
                sql="""
                
                
                SELECT FROM_UNIXTIME(date / 1000, '%Y-%m-%d') AS DATE,
                SUM(dau) AS dau,
                SUM(dnu) AS dnu
                FROM cfx_t.data_cpa_user_dau_2 
                WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))*1000 
                GROUP BY FROM_UNIXTIME(date / 1000, '%Y-%m-%d')
                ORDER BY FROM_UNIXTIME(date / 1000, '%Y-%m-%d') desc
                """
                data,columns = self.client.query(sql)
                #print(data)
                return self.tuple_list_to_html_table(data,columns)
            finally:
                self.client.close()

    def get_compare_data(self):
        # 方法1: 常规使用
        if self.client.connect():
            try:
                sql="""
                
                WITH new_data AS (
                    SELECT DATE,SUM(dau) AS dau_new,SUM(wau) AS wau_new,SUM(mau) AS mau_new,SUM(dnu) AS dnu_new,SUM(uv) AS uv_new FROM cfx_t.data_cpa_user_dau_2 
                    WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))*1000 GROUP BY DATE
                ),
                old_data AS (
                    SELECT DATE,SUM(dau) AS dau_old,SUM(wau) AS wau_old,SUM(mau) AS mau_old,SUM(dnu) AS dnu_old,SUM(uv) AS uv_old FROM cfx_t.data_valid_user_dau
                    WHERE DATE>=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY))*1000 GROUP BY DATE
                )
                SELECT FROM_UNIXTIME(a.date / 1000, '%Y-%m-%d') as date,
                a.dau_new,b.dau_old,(a.dau_new-b.dau_old) AS dau_diff,ROUND((a.dau_new-b.dau_old)/a.dau_new*100,2) AS dau_per,
                a.dnu_new,b.dnu_old,(a.dnu_new-b.dnu_old) AS dnu_diff,ROUND((a.dnu_new-b.dnu_old)/a.dnu_new*100,2) AS dnu_per
                FROM new_data AS a
                LEFT JOIN old_data AS b
                ON a.date=b.date
                ORDER BY a.date DESC
                """
                data,columns = self.client.query(sql)
                #print(data)
                return self.tuple_list_to_html_table(data,columns)
            finally:
                self.client.close()

    def tuple_list_to_html_table(self, data: list[tuple], headers: list[str]) -> str:
        """
        将 list[tuple] 转换为HTML表格

        :param data: MySQL查询结果，每个元组代表一行数据
        :param headers: 表头列表，与数据列数需一致
        :return: HTML表格字符串
        """
        # 检查表头与数据列数是否匹配
        if data and len(headers) != len(data[0]):
            raise ValueError("表头列数与数据列数不匹配")

        # 开始构建表格
        table = []
        # 表格开始标签（添加边框和样式，可选）
        table.append('<table border="1" cellpadding="8" style="border-collapse: collapse; margin: 20px 0;">')

        # 表头部分
        if headers:
            table.append('  <thead>')
            table.append('    <tr style="background-color: #f0f0f0;">')
            for header in headers:
                # 转义表头中的特殊字符（如&、<、>）
                escaped_header = html.escape(str(header))
                table.append(f'      <th>{escaped_header}</th>')
            table.append('    </tr>')
            table.append('  </thead>')

        # 表体部分
        table.append('  <tbody>')
        if not data:  # 处理空数据
            table.append('    <tr><td colspan="{}" style="text-align: center;">无数据</td></tr>'.format(len(headers) if headers else 1))
        else:
            for row in data:
                table.append('    <tr>')
                for item in row:
                    # 转义每个单元格的特殊字符
                    escaped_item = html.escape(str(row[item]))
                    table.append(f'      <td>{escaped_item}</td>')
                table.append('    </tr>')
        table.append('  </tbody>')

        # 表格结束标签
        table.append('</table>')

        # 拼接所有部分为完整HTML
        return '\n'.join(table)


# 使用示例
if __name__ == "__main__":


    # 方法1: 常规使用
    cpa = CpaData()
    result=cpa.get_compare_data()
    print(result)
